{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Neeps - Resit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "**Give the day and the time of the event co72002.L01.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>dow</th>\n",
       "        <th>tod</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monday</td>\n",
       "        <td>10:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Monday', '10:00')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT dow, tod FROM ut_event\n",
    "  WHERE id='co72002.L01';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "**For each event in module co72003 show the day, the time and the place.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>dow</th>\n",
       "        <th>tod</th>\n",
       "        <th>room</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72003.L01</td>\n",
       "        <td>Monday</td>\n",
       "        <td>14:00</td>\n",
       "        <td>cr.B12</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('co72003.L01', 'Monday', '14:00', 'cr.B12')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT ut_event.id, dow, tod, room FROM ut_event\n",
    "  WHERE ut_event.modle='co72003';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "**List the id of the events taught by 'Chisholm, Ken'.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22006.L01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22006.L02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22006.L03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22006.T01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co22006.T02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.L01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.L02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.T01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.T02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co72013.T06</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('co22006.L01',),\n",
       " ('co22006.L02',),\n",
       " ('co22006.L03',),\n",
       " ('co22006.T01',),\n",
       " ('co22006.T02',),\n",
       " ('co72013.L01',),\n",
       " ('co72013.L02',),\n",
       " ('co72013.T01',),\n",
       " ('co72013.T02',),\n",
       " ('co72013.T06',)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT ut_event.id\n",
    "  FROM ut_event JOIN ut_teaches ON (\n",
    "    ut_event.id=ut_teaches.event) JOIN\n",
    "    ut_staff ON (ut_teaches.staff=ut_staff.id)\n",
    "    WHERE ut_staff.name='Chisholm, Ken';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "**List the staff who teach in cr.SMH.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "16 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barclay, Ken</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chisholm, Ken</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cumming, Andrew</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Greig, Frank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hastie, Colin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jackson, Jim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kemmer, Rob</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lawson, Alistair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Macaulay, Catriona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Maclean, Aileen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>McEwan, Tom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Middleton, Steve</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Smith, Ian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Smyth, Michael</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Soutar, Alastair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Turner, Susan</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Barclay, Ken',),\n",
       " ('Chisholm, Ken',),\n",
       " ('Cumming, Andrew',),\n",
       " ('Greig, Frank',),\n",
       " ('Hastie, Colin',),\n",
       " ('Jackson, Jim',),\n",
       " ('Kemmer, Rob',),\n",
       " ('Lawson, Alistair',),\n",
       " ('Macaulay, Catriona',),\n",
       " ('Maclean, Aileen',),\n",
       " ('McEwan, Tom',),\n",
       " ('Middleton, Steve',),\n",
       " ('Smith, Ian',),\n",
       " ('Smyth, Michael',),\n",
       " ('Soutar, Alastair',),\n",
       " ('Turner, Susan',)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT ut_staff.name\n",
    "  FROM ut_staff JOIN ut_teaches ON (\n",
    "    ut_staff.id=ut_teaches.staff) JOIN\n",
    "    ut_event ON (ut_event.id=ut_teaches.event)\n",
    "    WHERE room='cr.SMH'\n",
    "    ORDER BY ut_staff.name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "**Show the total number of hours (over the whole semester) of classes for com.IS.a**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>hrs</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(60,)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(duration) hrs\n",
    "  FROM ut_event JOIN ut_occurs ON (\n",
    "    ut_event.id=ut_occurs.event) JOIN\n",
    "    ut_attends ON (ut_attends.event=ut_event.id)\n",
    "    WHERE student='com.IS.a';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
